/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Server.BUS;

import Server.DAO.DatabaseAccess;
import Server.DTO.*;
import java.util.ArrayList;
import java.util.List;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Trung
 */
public class QueryBUS {
        
    DatabaseAccess dbAccess;
    public static int SELECT_RESULT = 0;
    public QueryBUS()
    {
        dbAccess = new DatabaseAccess();        
    }
    
    public enum QueryType
    {
        SELECT_NOWHERE,
        SELECT_HAVEWHERE,
        INSERT,
        DELETE_NOWHERE,
        DELETE_HAVEWHERE,
        UPDATE_NOWHERE,
        UPDATE_HAVEWHERE,
        INVALID_QUERY
    };
    
    public QueryType checkQueryType(String queryString)
    {
        if(queryString.startsWith("SELECT"))//Select query
        {
            if(queryString.indexOf("WHERE") != -1)
                return QueryType.SELECT_HAVEWHERE;
            else
                return QueryType.SELECT_NOWHERE;
        }
        else
        {
            if(queryString.startsWith("INSERT"))//Insert query
                return QueryType.INSERT;
            else
            {
                if(queryString.startsWith("DELETE"))//Delete query
                {
                    if(queryString.indexOf("WHERE") != -1)
                        return QueryType.DELETE_HAVEWHERE;
                    else 
                        return QueryType.DELETE_NOWHERE;
                }
                else
                {
                    if(queryString.startsWith("UPDATE"))//Update query
                    {
                        if(queryString.indexOf("WHERE") != -1)
                            return QueryType.UPDATE_HAVEWHERE;
                        else 
                            return QueryType.UPDATE_NOWHERE;
                    }
                    else //InvalidQuery
                        return QueryType.INVALID_QUERY;
                }
            }
        }
    }
    
    public int insertExecute(Database database, InsertQuery insertQuery)
    {
        if(insertQuery.getTableName().endsWith(")"))//INSERT INTO SinhVien(colum1, column2)...
        {
            int end = insertQuery.getTableName().indexOf("(");
            String tableName = insertQuery.getTableName().substring(0, end);
            int length = insertQuery.getTableName().length();
            String lstCol = insertQuery.getTableName().substring(end + 1, length - 1);
            String[] colsName = lstCol.split(",");
            colsName = StringUlti.formatArray(colsName);
            
            if(isTableInDatabase(database, tableName))//Ktra Table co trong DB ?
            {                            
                String[] values = valuesResolve(insertQuery.getValuesString());
                
                Table table = new Table();
                //String tableName = insertQuery.getTableName();//*******
                String fileName = database.getPath() + "\\Tables\\" + tableName + "\\" + tableName + ".xml";//-Design
                table = dbAccess.readTableFromFile(fileName);

                //ArrayList<Column> colList = (ArrayList)table.getColumns();
                int colNumber = table.getColumns().size();
                if(checkInsertCondition(colsName, table.getColumns()))//Ktra dieu kien
                {                
                    List<Column> columns = new ArrayList<Column>();
                    int iValue = 0;
                    for(int i = 0; i < colNumber; i++)
                    {
                        if(table.getColumns().get(i).isIsPrimaryKey() || (table.getColumns().get(i).isAllowNull() == false))
                        {
                            int rowNumber = table.getColumns().get(i).getRowsList().size();
                            if(table.getColumns().get(i).getDataType().compareTo("int") == 0)//Int
                            {
                                if(checkDataType(values[iValue]))//Int
                                {
                                    Row row = new Row(rowNumber + 1, values[iValue]);
                                    iValue++;
                                    table.getColumns().get(i).getRowsList().add(row); 
                                }
                                else
                                    return 3;//DataType not proppriate                                         
                            }
                            else//DataType = vachar
                            {
                                if(values[iValue].startsWith("N'") || values[iValue].startsWith("'"))
                                {
                                    values[iValue] = formatStringValue(values[iValue]);
                                    Row row = new Row(rowNumber + 1, values[iValue]);
                                    iValue++;
                                    table.getColumns().get(i).getRowsList().add(row);
                                }
                                else
                                    return 3;//DataType not proppriate                                    
                            }                                                       
                        }
                        else// Columns allow Null
                        {
                            int rowNumber = table.getColumns().get(i).getRowsList().size();
                            Row row = new Row(rowNumber + 1, "null");
                            table.getColumns().get(i).getRowsList().add(row);
                        }
                    }

                    dbAccess.writeTableToFile(table, table.getPath());
                    
                    return 0;//Success
                }
                else
                {
                   return 2;//The properties are primary key or not allow null  not found
                }
                
            }
            else
            {
                return 1;//Can't find the Table in DB
            }
            
        }
        else//INSERT INTO SinhVien VALUES(...)
        {
            //String tableName1 = isTableInDatabase(database, insertQuery.getTableName());
            if(isTableInDatabase(database, insertQuery.getTableName()))//Ktra Table co trong database?
            {
                String[] values = valuesResolve(insertQuery.getValuesString());
                
                Table table = new Table();
                String tableName = insertQuery.getTableName();//*******
                String fileName = database.getPath() + "\\Tables\\" + tableName + "\\" + tableName + ".xml";//-Design
                table = dbAccess.readTableFromFile(fileName);
                
                //ArrayList<Column> colList = (ArrayList)table.getColumns(); 
                int colNumber = table.getColumns().size();
                for(int i = 0; i < colNumber; i++)
                {
                    int rowNumber = table.getColumns().get(i).getRowsList().size();
                    if(table.getColumns().get(i).getDataType().compareTo("int") == 0)//Int
                    {
                        if(checkDataType(values[i]))//Int
                        {
                            Row row = new Row(rowNumber +  1, values[i]);
                            table.getColumns().get(i).getRowsList().add(row);
                        }
                        else
                            return 3;//DataType not proppriate
                    }
                    else//DataType = vachar
                    {
                        if(values[i].startsWith("N'") || values[i].startsWith("'"))
                        {
                            values[i] = formatStringValue(values[i]);
                            Row row = new Row(rowNumber +  1, values[i]);
                            table.getColumns().get(i).getRowsList().add(row);
                        }
                        else
                            return 3;//DataType not proppriate
                        
                    }
                    
                }
                
                dbAccess.writeTableToFile(table, table.getPath());
                
                return 0;//Success 
            }
            else
            {
                return 1;//Can't find the Table in DB
            }
                    
        }
        
    }
    

    //Check Database have this table ?
    public boolean isTableInDatabase(Database db, String tableName)
    {
        String[] str = tableName.split(" ");        //Check case: TableA t1, TableB t2
        if(str.length == 2)
        {
            int tablesNumber = db.getTables().size();
            for(int i = 0; i < tablesNumber; i++)
            {
                if(db.getTables().get(i).getName().toString().compareTo(str[0]) == 0)
                {
                    return true;
                }
            }
            return false;
        }
        else
        {
            int tablesNumber = db.getTables().size();
            for(int i = 0; i < tablesNumber; i++)
            {
                if(db.getTables().get(i).getName().toString().compareTo(tableName) == 0)
                {
                    return true;
                }
            }
            return false;
        }
    }
    
    //Check Database have this tables ?
    public boolean isTablesInDatabase(Database db, String[] tableNames)
    {
        int n = tableNames.length;
        for(int i = 0; i < n; i++)
        {
            if(isTableInDatabase(db, tableNames[i]) == false)
                return false;
        }
        return true;
    }
    
    public String[] valuesResolve(String valueString) 
    {
        int length = valueString.length();
        String valuesString = valueString.substring(1, length - 1);//values (a, b, c): bo ()
        String[] values = valuesString.split(",");//Tach a, b, c rieng
        values = StringUlti.formatArray(values);
        
        return values;
    }
    
    public String formatStringValue(String strValue)
    {
        String result = StringUlti.formatString(strValue);        
        if(result.startsWith("N'"))
        {
            result = result.substring(2, result.length() - 1);
            result = StringUlti.formatString(result);
        }
        else
        {
            if(result.startsWith("'"))
            {
                result = result.substring(1, result.length() - 1);
                result = StringUlti.formatString(result);
            }
            
        }
        return result;
    }
    
    public boolean checkInsertCondition(String[] colsName, List<Column> colList)
    {
        int n = colList.size();
        List<String> columns = new ArrayList<String>();        
        
        for(int i = 0; i < n; i++)
        {
            if(colList.get(i).isIsPrimaryKey() || (colList.get(i).isAllowNull() == false))//Cac cot la khoa chinh hay Not null fai co trong colsName
            {
                columns.add(colList.get(i).getName());
            }
        }
        
        int m = colsName.length;
        for(int i = 0; i < m; i++)
        {
            if(columns.get(i).compareTo(colsName[i]) != 0)
                return false;
        }
        return true;
    }
    
    //Check data type of input values. true: int || false: string
    public boolean checkDataType(String value){      
        try {                  
            Integer.parseInt(value);  
            return true;  
        } catch(Exception e) {  
            return false;  
        }         
    }
    
    public DefaultTableModel selectNoWhereExecute(Database db, SelectNoWhereQuery selectQuery)
    {
        DefaultTableModel model = new DefaultTableModel();
        
        String selectString = StringUlti.formatString(selectQuery.getSelectString());
        String fromString = StringUlti.formatString(selectQuery.getFromString());
        String[] tNames = fromString.split(",");
        tNames = StringUlti.formatArray(tNames);
        MyTableName[] mtNames = new MyTableName[tNames.length];
        mtNames = this.tableNameArrayResolve(tNames);
        
        if (selectString.indexOf("*") != -1)//SELECT * ....
        {
            if (isTablesInDatabase(db, tNames))//Ktra Tables co trong Database?
            {
                Table table = mergeTables(db, mtNames);//Merge Tables

                List<Column> colList = (ArrayList) table.getColumns();
                int nCol = colList.size();
                int nRow = colList.get(0).getRowsList().size();
                String[] columns = new String[nCol];
                String[][] data = new String[nRow][nCol];

                for (int i = 0; i < nCol; i++) {
                    columns[i] = colList.get(i).getName();
                    int rowNumber = colList.get(i).getRowsList().size();
                    for (int j = 0; j < rowNumber; j++) 
                    {
                        int rowN = colList.get(i).getRowsList().size();//So luong dong cua moi cot khong bang nhau
                            if(j < rowN)
                            {
                                Row row = new Row(j + 1, colList.get(i).getRowsList().get(j).getText());
                                data[j][i] = row.getText();
                            }
                            else
                                data[j][i] = "";
                    }
                }

                model.setDataVector(data, columns);
            }
            else
                QueryBUS.SELECT_RESULT = 1;//Table(s) not exist in Database 
        }
        else//SELECT t1.X, t1.Y, t2.A, t2.B FROM Tabel1 t1, Table2 t2
        {
            if (isTablesInDatabase(db, tNames))//Ktra Tables co trong Database?
            {
                
                //Xu ly fromString => luu mang MyTableName
                //Xu ly selectString -> lstCol
                //Duyet lstCol xd thuoc table nao
                //Add vao lstCol
                List<Table> lstTable = this.getTablesInfo(db, mtNames);
                
                MyColumnName[] mColNames = new MyColumnName[tNames.length];
                String[] colNames = selectString.split(",");
                colNames = StringUlti.formatArray(colNames);
                mColNames = this.columnNameArrayResolve(colNames);
                
                List<Column> lstCol = new ArrayList<Column>();
                if(this.checkColumnsExistInTables(mColNames, mtNames, lstTable))//Ktra danh sach cac cot co ton tai trong Table list
                { 
                    int tableNumber = lstTable.size();
                    for(int i = 0; i < mColNames.length; i++)//Duyet danh sach cac cot select
                    {
                        for(int j = 0; j < tableNumber; j++)//Duyet cac table trong table list
                        {
                            int colNumber = lstTable.get(j).getColumns().size();
                            
                            for(int col = 0; col < colNumber; col++)//Duyet cac cot trong tung table
                            {
                                Column column = lstTable.get(j).getColumns().get(col);
                                //Kiem tra ten Column va Ten viet tat cua bang
                                if((column.getName().compareTo(mColNames[i].name) == 0) && (mColNames[i].sTableName.compareTo(mtNames[j].s_name) == 0)) 
                                {
                                    lstCol.add(column);
                                }
                            }
                        }

                    }
                    
                    int nCol = lstCol.size();
                    int nRow = this.getMaxRow(lstCol);//Tim max
                    String[] columns = new String[nCol];                    
                    String[][] data = new String[nRow][nCol];
                    for(int i = 0; i < nCol; i++)
                    {
                        columns[i] = lstCol.get(i).getName();
                        for(int j = 0; j < nRow; j++)
                        {
                            int rowN = lstCol.get(i).getRowsList().size();
                            if(j < rowN)
                            {
                                Row row = new Row(j + 1, lstCol.get(i).getRowsList().get(j).getText());
                                data[j][i] = row.getText();
                            }
                            else
                                data[j][i] = "";
                        }
                    }
                    
                    model.setDataVector(data, columns);
                }
                else
                    QueryBUS.SELECT_RESULT = 2;//Column(s) not exist in Tables list
                
            }
            else
                QueryBUS.SELECT_RESULT = 1;//Table(s) not exist in Database 
        }
        
        return model;
    }
    
    public int getMaxRow(List<Column> lstCol)
    {
        int n = lstCol.size();
        int max = lstCol.get(0).getRowsList().size();
        for(int i = 1; i < n; i++)
        {
            if(max < lstCol.get(i).getRowsList().size())
                max = lstCol.get(i).getRowsList().size();
        }
        return max;
    }
    
    public boolean checkColumnsExistInTables(MyColumnName[] mColNames, MyTableName[] mtNames, List<Table> lstTable )
    {
        int n = lstTable.size();
        for(int i = 0; i < mColNames.length; i++)
        {
            for(int j = 0; j < mtNames.length; j++)
            {
                if(mColNames[i].sTableName.compareTo(mtNames[j].s_name) == 0)
                {
                    if(checkAColumnInTable(mColNames[i].name, lstTable.get(j)) == false)
                        return false;
                }
//                if(checkAColumnInTable(mColNames[i].name, lstTable.get(j)) == false)
//                {
//                    if(j == (n-1))//Duyet qua bang cuoi cung
//                        return false;
//                }
            }
            
        }
        return true;
    }
    
    //Ktra Column nay co trong Table ?
    public boolean checkAColumnInTable(String colName, Table table)
    {
        int n = table.getColumns().size();
        for(int i = 0; i < n; i++)
        {
            if(colName.compareTo(table.getColumns().get(i).getName()) == 0)
                return true;
        }
        return false;
    }
    //
    public List<Table> getTablesInfo(Database db, MyTableName[] mtNames)
    {
        List<Table> lstTable = new ArrayList<Table>();
        Table table = new Table();
        String fileName = "";
        for(int i = 0; i < mtNames.length; i++)
        {
            fileName = db.getPath() + "\\Tables\\" + mtNames[i].name + "\\" + mtNames[i].name + ".xml";
            table = dbAccess.readTableFromFile(fileName);
            lstTable.add(table);
        }
        
        return lstTable;
    }
    
    //t1.ColumnName
    public MyColumnName columnNameResolve(String colName)
    {        
        MyColumnName mColName = new MyColumnName();
        int begin = colName.indexOf(".");
        
        mColName.sTableName = colName.substring(0, begin);
        mColName.name = colName.substring(begin + 1);
        
        return mColName;
    }
    
    public MyColumnName[] columnNameArrayResolve(String[] colNames)
    {
        MyColumnName[] result = new MyColumnName[colNames.length];
        for(int i = 0; i < colNames.length; i++)
        {
            result[i] = this.columnNameResolve(colNames[i]);
        }
        
        return result;
    }
    
    //Resolve TableA t1
    public MyTableName tableNameResolve(String tableName)
    {
        String str = StringUlti.formatString(tableName);
        String[] result = str.split(" ");
        MyTableName  mTableName = new MyTableName();
        if(result.length > 1)
        {             
            mTableName.name = result[0];
            mTableName.s_name = result[1];
        }
        else
            mTableName.name = tableName;
        
        return mTableName;
    }
    
    //Resolve TableA t1(, TableB, t2)
    public MyTableName[] tableNameArrayResolve(String[] tableNames)
    {
        MyTableName[] result = new MyTableName[tableNames.length];
        for(int i = 0; i < tableNames.length; i++)
        {
            result[i] = tableNameResolve(tableNames[i]);
        }
        return result;
    }
    
    public Table mergeTables(Database db, MyTableName[] mNames)
    {
        Table resTable = new Table();        
        Table table = new Table();
        String fileName = "";
        List<Column> lstCol = new ArrayList<Column>();
        Column col = new Column();
        for(int i = 0; i < mNames.length; i++)
        {
            fileName = db.getPath() + "\\" + "\\Tables\\" + mNames[i].name + "\\" + mNames[i].name + ".xml";
            table = dbAccess.readTableFromFile(fileName);
            
            int nCol = table.getColumns().size();
            for (int j = 0; j < nCol; j++)
            {
                col = table.getColumns().get(j);
                lstCol.add(col);
            }
        }
        
        resTable.setColumns(lstCol);
        
        return resTable;
    }
    
    
    
    //********************************
    
    //Anh xa TableName voi ten viet tat
    class MyTableName
    {
        private String name;
        private String s_name;//Ten viet t?t
        
        public MyTableName()
        {
            name = "";
            s_name = "";
        }

        /**
         * @return the name
         */
        public String getName() {
            return name;
        }

        /**
         * @param name the name to set
         */
        public void setName(String name) {
            this.name = name;
        }

        /**
         * @return the s_name
         */
        public String getS_name() {
            return s_name;
        }

        /**
         * @param s_name the s_name to set
         */
        public void setS_name(String s_name) {
            this.s_name = s_name;
        }
    }
    
    
    //********************************
    //Kiem tra thuoc tinh Khoa chinh
    class PrimaryKey
    {
        private List<Column> lstKey;
        public PrimaryKey()
        {
            lstKey = new ArrayList<Column>();
        }

        /**
         * @return the lstKey
         */
        public List<Column> getLstKey() {
            return lstKey;
        }

        /**
         * @param lstKey the lstKey to set
         */
        public void setLstKey(List<Column> lstKey) {
            this.lstKey = lstKey;
        }
    }
    
    //********************************
    //t1.ColumnName
    class MyColumnName
    {
        private String name;
        private String sTableName;//Ten viet tat cua table
        
        public MyColumnName()
        {
            name = "";
            sTableName = "";
        }
    }
}
